package com.cnpc.framework.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;


/**
 * excel导入-解析工具类
 *
 * @author qihh
 */
public class ExcelImportUtil {

    public static FormulaEvaluator formulaEvaluator = null;
    /**
     * 解析excel文件，封装解析数据
     */
    public static List<Map<String, Object>> processWorkBook(Workbook workbook, List<Map<String, Object>> result, String[] headers) {
        formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
        // 3.遍历集合，组装结果
        int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量
        // 遍历每个Sheet
        for (int s = 0; s < sheetCount; s++) {
            Sheet sheet = workbook.getSheetAt(s);
            int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数
            if (rowCount == 0) {
                break;
            }
            int cellIndex = sheet.getRow(0).getPhysicalNumberOfCells(); //设置总列数
            // 遍历每一行
            for (int r = 1; r < rowCount; r++) {
                Row row = sheet.getRow(r);
                System.out.println("index:" + r);
                Map<String, Object> map = parseRow(row, headers.length, headers);
                if (!CollectionUtils.isEmpty(map)) {
                    result.add(map);
                }
            }
        }
        return result;
    }

    /**
     * 解析excel文件，封装解析数据
     */
    public static List<Map<String, Object>> processWorkBook(Workbook workbook, List<Map<String, Object>> result, String[] headers, int sheetIndex, int rowStart) {
        formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
        Sheet sheet = workbook.getSheetAt(sheetIndex-1);
        int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数
        if (rowCount == 0) {
            return null;
        }
        int cellIndex = sheet.getRow(0).getPhysicalNumberOfCells(); //设置总列数
        // 遍历每一行
        for (int r = rowStart-1; r < rowCount; r++) {
            System.out.println("rowIndex:"+r);
            Row row = sheet.getRow(r);
            Map<String, Object> map = parseRow(row, headers.length, headers);
            if (!CollectionUtils.isEmpty(map)) {
                result.add(map);
            }
        }

        return result;
    }

    /**
     * 解析每行数据
     *
     * @param row       当前行数据
     * @param cellIndex 总列数
     */
    private static Map<String, Object> parseRow(Row row, int cellIndex, String[] headers) {
        Map<String, Object> map = new LinkedHashMap<>();
        if (row == null) {
            return null;
        }
        // 遍历每一列
        for (int index = 0; index < cellIndex; index++) {
            String cellStringValue;
            try {
                System.out.println("index:"+index);
                Cell cell = row.getCell(index);
                if (cell == null) {
                    map.put(headers[index], "");
                    continue;
                }

                int cellType = cell.getCellType();
                System.out.println("cellType:"+cellType);

                switch (cellType) {
                    case Cell.CELL_TYPE_STRING: // 文本
                    {
                        cellStringValue = cell.getStringCellValue();
                        break;
                    }
                    case Cell.CELL_TYPE_NUMERIC: // 数字、日期
                    {
                        if (DateUtil.isCellDateFormatted(cell)) {
                            SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
                            cellStringValue = fmt.format(cell.getDateCellValue()); // 日期型
                        } else {


                            Double d = cell.getNumericCellValue();
                            DecimalFormat df = new DecimalFormat("#.##");
                            cellStringValue = df.format(d);

                            /*cellStringValue = String.valueOf(cell.getNumericCellValue()); // 数字
                            if (cellStringValue.contains("E")) {
                                cellStringValue = String.valueOf(new Double(cell.getNumericCellValue()).longValue()); // 数字
                            }*/
                        }
                        break;
                    }
                    case Cell.CELL_TYPE_BOOLEAN: // 布尔型
                    {
                        cellStringValue = String.valueOf(cell.getBooleanCellValue());
                        break;
                    }
                    case Cell.CELL_TYPE_BLANK: // 空白
                    {
                        cellStringValue = cell.getStringCellValue();
                        break;
                    }
                    case Cell.CELL_TYPE_ERROR: // 错误
                    {
                        cellStringValue = null;
                        break;
                    }
                    case Cell.CELL_TYPE_FORMULA: // 公式
                    {
                        String cellFormula = cell.getCellFormula();
                        System.out.println("CellFormula:"+ cellFormula);
                        if (cellFormula.contains(",)")) {
                            cell.setCellFormula(cellFormula.replace(",)",",0)"));
                        }
                        CellValue evaluate = formulaEvaluator.evaluate(cell);
                        cellStringValue = evaluate.formatAsString();
                        break;
                    }
                    default: {
                        cellStringValue = null;
                    }
                }
                System.out.println(cellStringValue);
                /*if (null == cellStringValue) {
                    return null;
                }*/

                map.put(headers[index], cellStringValue);
            } catch (Exception e) {
                e.printStackTrace();
                System.out.println("Exception:"+index+":"+headers[index]);
                return null;
            }

        }

        return map;
    }



    public static List<Map<String, Object>> getExcelToListObject(MultipartFile file, String[] headers) throws Exception {
        FileInputStream fileInputStream = (FileInputStream) file.getInputStream();
        Workbook workbook = WorkbookFactory.create(fileInputStream);
        List<Map<String, Object>> listq = new LinkedList<>();
        List<Map<String, Object>> maps = ExcelImportUtil.processWorkBook(workbook, listq, headers);
        return maps;
    }

    public static List<Map<String, Object>> getExcelToListObject(File file, String[] headers) throws Exception {
        FileInputStream fileInputStream = new FileInputStream(file);
        Workbook workbook = WorkbookFactory.create(fileInputStream);
        List<Map<String, Object>> listq = new LinkedList<>();
        List<Map<String, Object>> maps = ExcelImportUtil.processWorkBook(workbook, listq, headers);
        return maps;
    }

    public static List<Map<String, Object>> getExcelToListObject(MultipartFile file, String[] headers, int sheetIndex, int rowStart) throws Exception {
        FileInputStream fileInputStream = (FileInputStream) file.getInputStream();
        Workbook workbook = WorkbookFactory.create(fileInputStream);
        List<Map<String, Object>> listq = new LinkedList<>();
        List<Map<String, Object>> maps = ExcelImportUtil.processWorkBook(workbook, listq, headers,sheetIndex,rowStart);
        return maps;
    }

    public static List<Map<String, Object>> getExcelToListObject(File file, String[] headers, int sheetIndex, int rowStart) throws Exception {
        FileInputStream fileInputStream = new FileInputStream(file);
        Workbook workbook = WorkbookFactory.create(fileInputStream);
        List<Map<String, Object>> listq = new LinkedList<>();
        List<Map<String, Object>> maps = ExcelImportUtil.processWorkBook(workbook, listq, headers,sheetIndex,rowStart);
        return maps;
    }

}
